<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */

class Modules_Menu_Models_Mysql_Menu extends Modules_Menu_Models_Menu
{	
	protected $_table = 'menu';
	
	public function getById($id)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . $this->_table . ' 
							WHERE menu_id = "' . $this->_conn->escape($id) . '"';
		$sql .= ' LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}

	public function add($menu)
	{
		return $this->_conn->insert($menu, $this->_conn->_tablePrefix . $this->_table);	
	}
	
	public function find($offset = null, $limit = null, $condition = array())
	{
		$sql = 'SELECT m.* FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS m
				WHERE m.language = "' . $this->_conn->escape($this->getLang()) . '"';

		$sql .= ' ORDER BY m.created_date DESC, m.menu_id DESC';
		if ($offset !== false && $limit !== false) {
			$sql .= ' LIMIT ' . (int)$offset . ',' . (int)$limit;
		}
		$rs = $this->_conn->query($sql);
		$menus = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$menus[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $menus;
	}
	
	public function count($condition = array())
	{
		/**
		 * Language is index column 
		 */
		$sql = 'SELECT COUNT(a.language) as num_menus FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS m
				WHERE m.language = "' . $this->_conn->escape($this->getLang()) . '"';	

		$rs = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $row['num_menus'];
	}
	
	public function getAllMenus()
	{
		$sql = 'SELECT m.* FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS m
				WHERE m.language = "' . $this->_conn->escape($this->getLang()) . '"';
		$sql .= ' ORDER BY m.created_date DESC, m.menu_id DESC';
		$rs = $this->_conn->query($sql);
		$menus = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$menus[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $menus;
	}
	
	public function update($menu)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . $this->_table . '  
					SET name = "%s", description = "%s", language = "%s"
					WHERE menu_id = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($menu['name']),
							$this->_conn->escape($menu['description']),
							$this->_conn->escape($menu['language']),	
							$this->_conn->escape($menu['menu_id']));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();	
	}
	
	public function delete($id)
	{
		$sql = 'DELETE FROM ' . $this->_conn->_tablePrefix . $this->_table . ' 
							WHERE menu_id = ' . $this->_conn->escape($id);
		$rs = $this->_conn->query($sql);
		$affectRow = $this->_conn->affectedRows();
		$this->_conn->freeResult($rs);
		return $affectRow;
	}
	
	/* ========== For translation =========================================== */
	
	public function getTranslations($item, $lang = null)
	{
		$sql  = sprintf("SELECT m.* FROM " . $this->_conn->_tablePrefix . $this->_table . " AS m
						INNER JOIN 
						(
							SELECT tr1.* FROM " . $this->_conn->_tablePrefix . "core_translation AS tr1
							INNER JOIN " . $this->_conn->_tablePrefix . "core_translation AS tr2 
								ON (tr1.item_id = '%s' AND tr1.source_item_id = tr2.item_id) 
								OR (tr2.item_id = '%s' AND tr1.item_id = tr2.source_item_id)
								OR (tr1.source_item_id = '%s' AND tr1.source_item_id = tr2.source_item_id)
							WHERE tr1.item_class = '%s' AND tr2.item_class = '%s'
							GROUP by tr1.translation_id
						) AS tr
							ON tr.item_id = m.menu_id", 
						$this->_conn->escape($item['menu_id']), 
						$this->_conn->escape($item['menu_id']), 
						$this->_conn->escape($item['menu_id']),
						'Modules_Menu_Models_Menu',
						'Modules_Menu_Models_Menu');
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}

	public function getTranslatable($lang, $count = null)
	{
		$sql  = sprintf('SELECT m.*, (tr.item_id IS NULL) AS translatable 
						FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS m
						LEFT JOIN ' . $this->_conn->_tablePrefix . 'core_translation AS tr
							ON tr.source_item_id = m.menu_id
							AND tr.item_class = "%s"
							AND tr.language = "%s"
						WHERE m.language = "%s"
						ORDER BY m.menu_id DESC',
						'Modules_Menu_Models_Menu',
						$this->_conn->escape($this->_lang),
						$this->_conn->escape($this->_lang));
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function getSource($menu)
	{
		$sql = sprintf("SELECT m.* 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS m
						INNER JOIN " . $this->_conn->_tablePrefix . "core_translation AS tr
							ON m.menu_id = tr.source_item_id
						WHERE tr.item_id = '%s' AND tr.item_class = '%s'
						LIMIT 1", 
						$this->_conn->escape($menu['menu_id']),
						'Modules_Menu_Models_Menu');
						
		$rs = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
}